﻿using System;
using System.Data;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Collections.Generic;
using BTN04.DTO;
using System.Data.SqlClient;

namespace BTN04.DAO
{
    public class SanPhamDAO
    {
        public List<SanPhamDTO> DanhSachSanPhamTheoLoai(int _maLoai)
        {
            List<SanPhamDTO> _danhSach = new List<SanPhamDTO>();
            SqlConnection cnn = new SqlConnection(DataProvider.ConnectionString);
            string str = "SELECT * FROM SANPHAM WHERE MaLoaiSanPham = " + _maLoai.ToString();
            cnn.Open();
            SqlCommand comand = new SqlCommand(str, cnn);
            SqlDataReader read = comand.ExecuteReader();
            while (read.Read())
            {
                SanPhamDTO spDTO = new SanPhamDTO();
                spDTO.MaSanPham = int.Parse(read["MaSanPham"].ToString());
                spDTO.TenSanPham = read["TenSanPham"].ToString();
                spDTO.MaNhaSanXuat = int.Parse(read["MaNhaSanXuat"].ToString());
                spDTO.MaTinhTrangSanPham = int.Parse(read["MaTinhTrangSanPham"].ToString());
                spDTO.SoLuongXem = int.Parse(read["SoLuongXem"].ToString());
                spDTO.MaXuatSu = int.Parse(read["MaXuatSu"].ToString());
                spDTO.MaLoaiSanPham = int.Parse(read["MaLoaiSanPham"].ToString());
                spDTO.GiaBan = int.Parse(read["GiaBan"].ToString());
                spDTO.Hinh = read["HinhAnh"].ToString();
                spDTO.MaKhuyenMai = int.Parse(read["MaKhuyenMai"].ToString());
                _danhSach.Add(spDTO);

            }
            return _danhSach;
        }

        public List<SanPhamDTO> DanhSachSanPhamCungLoai(int _maLoai)
        {
            List<SanPhamDTO> _danhSach = new List<SanPhamDTO>();
            SqlConnection cnn = new SqlConnection(DataProvider.ConnectionString);
            string str = "SELECT Top 6 MaSanPham,TenSanPham,GiaBan,HinhAnh,MaKhuyenMai,MaLoaiSanPham FROM SANPHAM WHERE MaLoaiSanPham = " + _maLoai.ToString();
            cnn.Open();
            SqlCommand comand = new SqlCommand(str, cnn);
            SqlDataReader read = comand.ExecuteReader();
            while (read.Read())
            {
                SanPhamDTO spDTO = new SanPhamDTO();
                spDTO.MaSanPham = int.Parse(read["MaSanPham"].ToString());
                spDTO.TenSanPham = read["TenSanPham"].ToString();
               // spDTO.MaNhaSanXuat = int.Parse(read["MaNhaSanXuat"].ToString());
               // spDTO.MaTinhTrangSanPham = int.Parse(read["MaTinhTrangSanPham"].ToString());
               // spDTO.SoLuongXem = int.Parse(read["SoLuongXem"].ToString());
                //spDTO.MaXuatSu = int.Parse(read["MaXuatSu"].ToString());
                spDTO.MaLoaiSanPham = int.Parse(read["MaLoaiSanPham"].ToString());
                spDTO.GiaBan = int.Parse(read["GiaBan"].ToString());
                spDTO.Hinh = read["HinhAnh"].ToString();
                spDTO.MaKhuyenMai = int.Parse(read["MaKhuyenMai"].ToString());
                _danhSach.Add(spDTO);
            }
            return _danhSach;
        }


        public List<SanPhamDTO> SanPhamXemNhieuTrongNgay()
        {
            List<SanPhamDTO> _danhSach = new List<SanPhamDTO>();
            SqlConnection cnn = new SqlConnection(DataProvider.ConnectionString);
            string str = "SELECT TOP 10 * FROM SANPHAM ";
            //str += " Limit 15";
            cnn.Open();
            SqlCommand comand = new SqlCommand(str, cnn);
            SqlDataReader read = comand.ExecuteReader();
            while (read.Read())
            {
                SanPhamDTO spDTO = new SanPhamDTO();
                spDTO.MaSanPham = int.Parse(read["MaSanPham"].ToString());
                spDTO.TenSanPham = read["TenSanPham"].ToString();
                spDTO.MaNhaSanXuat = int.Parse(read["MaNhaSanXuat"].ToString());
                spDTO.MaTinhTrangSanPham = int.Parse(read["MaTinhTrangSanPham"].ToString());
                spDTO.SoLuongXem = int.Parse(read["SoLuongXem"].ToString());
                spDTO.MaXuatSu = int.Parse(read["MaXuatSu"].ToString());
                spDTO.MaLoaiSanPham = int.Parse(read["MaLoaiSanPham"].ToString());
                spDTO.GiaBan = int.Parse(read["GiaBan"].ToString());
                spDTO.Hinh = read["HinhAnh"].ToString();
                spDTO.MaKhuyenMai = int.Parse(read["MaKhuyenMai"].ToString());
                _danhSach.Add(spDTO);
            }
            return _danhSach;
        }

        public List<SanPhamDTO> SanPhamNew()
        {
            List<SanPhamDTO> _danhSach = new List<SanPhamDTO>();
            SqlConnection cnn = new SqlConnection(DataProvider.ConnectionString);
            string str = "SELECT TOP 5 * FROM SANPHAM ";
           // str += " ORDER BY NgayNhap DESC";
           // str += " LIMIT 5";
            cnn.Open();
            SqlCommand comand = new SqlCommand(str, cnn);
            SqlDataReader read = comand.ExecuteReader();
            while (read.Read())
            {
                SanPhamDTO spDTO = new SanPhamDTO();
                spDTO.MaSanPham = int.Parse(read["MaSanPham"].ToString());
                spDTO.TenSanPham = read["TenSanPham"].ToString();
                spDTO.MaNhaSanXuat = int.Parse(read["MaNhaSanXuat"].ToString());
                spDTO.MaTinhTrangSanPham = int.Parse(read["MaTinhTrangSanPham"].ToString());
                spDTO.SoLuongXem = int.Parse(read["SoLuongXem"].ToString());
                spDTO.MaXuatSu = int.Parse(read["MaXuatSu"].ToString());
                spDTO.MaLoaiSanPham = int.Parse(read["MaLoaiSanPham"].ToString());
                spDTO.GiaBan = int.Parse(read["GiaBan"].ToString());
                spDTO.Hinh = read["HinhAnh"].ToString();
                spDTO.MaKhuyenMai = int.Parse(read["MaKhuyenMai"].ToString());
                spDTO.MoTa = read["MoTa"].ToString();                
                _danhSach.Add(spDTO);
            }            
            return _danhSach;
        }

        public DataTable LaySanPhamTheoMa(string _maSanPham)
        {
            SqlConnection cnn = new SqlConnection(DataProvider.ConnectionString);
            string str = "SELECT *";
            str += " FROM SANPHAM sp,NhaSanXuat nsx,LoaiSanPham lsp,XuatSu xs ";
            str += " WHERE sp.MaNhaSanXuat = nsx.MaNhaSanXuat AND sp.MaLoaiSanPham = lsp.MaLoaiSanPham AND sp.MaXuatSu = xs.MaXuatSu AND sp.MaSanPham = " + _maSanPham;
            cnn.Open();
            SqlCommand comand = new SqlCommand(str, cnn);
            SqlDataAdapter adapter = new SqlDataAdapter(comand);
            DataTable dt = new DataTable();
            adapter.Fill(dt);
            return dt;
        }
        //Tim kiem san pham
        public DataTable  TimKiem(SanPhamCrt spCrt)
        {
            SqlCommand cmd = new SqlCommand();
            string dkTen = "1=1";
            string dkLoaiSanPham = "1=1";
            string dkGia = "1=1";

            if (spCrt.ChkTen == true)
            {
                dkTen = " SP.Ten like ? ";
                cmd.Parameters.Add("@TenSP",SqlDbType.NVarChar );
                cmd.Parameters["@TenSP"].Value = "%" + spCrt.TenSanPham + "%";
            }
            if (spCrt.ChkLoaiSanPham == true)
            {
                dkLoaiSanPham = " SP.MaLoaiSanPham= ? ";
                cmd.Parameters.Add("@MaLoaiSP",SqlDbType.Int );
                cmd.Parameters["@MaLoaiSP"].Value = spCrt.MaLoaiSanPham;
            }

            if (spCrt.ChkGia == true)
            {
                dkGia = " SP.GiaBan between ? and ? ";
                cmd.Parameters.Add("@GiaTu", SqlDbType.Float);
                cmd.Parameters.Add("@GiaDen", SqlDbType.Float);
                cmd.Parameters["@GiaTu"].Value = spCrt.GiaTu;
                cmd.Parameters["@GiaDen"].Value = spCrt.GiaDen;
            }

            string dkWhere = " Where ";
            dkWhere += dkTen;
            dkWhere += " And " + dkLoaiSanPham;
            dkWhere += " And " + dkGia;

            string sql = "Select *";
            sql += " FROM SANPHAM sp,NhaSanXuat nsx,LoaiSanPham lsp,XuatSu xs";
            sql += dkWhere;
            sql+= " And sp.MaNhaSanXuat = nsx.MaNhaSanXuat AND sp.MaLoaiSanPham = lsp.MaLoaiSanPham AND sp.MaXuatSu = xs.MaXuatSu ";
            SqlConnection cnn = new SqlConnection(DataProvider.ConnectionString);
            cnn.Open();
            cmd = new SqlCommand(sql ,cnn);
            SqlDataAdapter adapter = new SqlDataAdapter(cmd);
            DataTable dt = new DataTable();
            adapter.Fill(dt);
            return dt;
        }

        public DataTable TimKiemNangCao(string TenSanPham,int GiaTu,int GiaDen,int MaNhaSanXuat,int MaLoaiSanPham,int MaXuatSu)
        {
            string sql = "SELECT * FROM SanPham WHERE TenSanPham like '%" +TenSanPham +"%' ";
            SqlConnection cnn = new SqlConnection(DataProvider.ConnectionString);

            cnn.Open();
            if (MaLoaiSanPham != 0)
                sql += " AND MaLoaiSanPham =" + MaLoaiSanPham;
            if (MaNhaSanXuat != 0)
                sql += " AND MaNhaSanXuat=" + MaNhaSanXuat;
            if (MaXuatSu != 0)
                sql += " AND MaXuatSu=" + MaXuatSu;           
            if(GiaTu != 0 && GiaDen != 0)
                sql += " AND GiaBan  between " + GiaTu + " AND " + GiaDen;
            else
            {
                if (GiaTu == 0 && GiaDen!= 0)
                {
                    sql += " AND GiaBan < " + GiaDen;
                }
                if (GiaTu != 0 && GiaDen == 0)
                {
                    sql += " AND GiaBan > " + GiaTu;
                }
            }
            SqlCommand comand = new SqlCommand(sql, cnn);
            SqlDataAdapter Adapter = new SqlDataAdapter(comand);
            DataTable dt = new DataTable();
            Adapter.Fill(dt);
            return dt;
            
        }
        
    }
}
